package yu.zhou.njupt.importdata.core.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import yu.zhou.njupt.importdata.core.Importer;
import yu.zhou.njupt.importdata.entity.ImportResult;
import yu.zhou.njupt.importdata.entity.enumtype.CreateTable;
import yu.zhou.njupt.importdata.entity.enumtype.DoTruncate;
import yu.zhou.njupt.importdata.entity.enumtype.NewImport;
import yu.zhou.njupt.importdata.util.RegexUtil;

/** 
 * 用于导入xlsx文件，
 * 文件名以"$-"开始，一个文件对应一张表，文件名为表名，文件里的多个sheet全是同一张表的数据。
 * 文件名不以"$-"，文件里的每个sheet对应一张表，sheet名为表名。
**/
public class XlsxImporter extends Importer{
    
    public XlsxImporter(Connection conn, DoTruncate doTruncate, NewImport newImport, CreateTable createTable, int titleNum) throws Exception {
        super(conn, doTruncate, newImport, createTable, titleNum);
    }

    public ImportResult importData(File sourceFile,
            List<String> disableConsStrSet, List<String> enableConsStrSet, 
            List<String> disableIndexesStrSet, List<String> enableIndexesStrSet){
        int count = 0;
        int recordCount = 0;
        long elapsedTime = 0;
        ImportResult importResult = null;
        PreparedStatement ps = null;
        FileInputStream fis = null;
        String filename = null;
        String sheetName = null;

		try {
            filename = sourceFile.getName();
            String tableName = null;
            boolean isSingleTable = filename.startsWith("$-");
            if (isSingleTable) {
                tableName = RegexUtil.getValidName(filename).toUpperCase();
            }
		    fis = new FileInputStream(sourceFile);
		    XSSFWorkbook workbook = new XSSFWorkbook(fis);
		    XSSFSheet sheet = null;
		    
		    for (int sheetIndex = 0; ;sheetIndex++) {
                try {
                    sheet = workbook.getSheetAt(sheetIndex);
                    sheetName = sheet.getSheetName();
                } catch (Exception e) {
                    break;
                }
                Date startTime = new Date();
                if (!hasDealt(filename, sheetName)) {
                    if (!isSingleTable) {
                        tableName = RegexUtil.getValidName(sheetName).toUpperCase();
                    }
                    System.out.println("    Sheet:" + sheetName + "(表：" + tableName + ")...");
                    
                    XSSFRow titleRow = sheet.getRow(0);
                    int columnNum = titleRow.getLastCellNum() - titleRow.getFirstCellNum(); // getFirstCellNum返回0,getLastCellNum()返回列数
                    columnIndexList = new ArrayList<Integer>();
                    List<String> cellNameList = new ArrayList<String>();
                    for (int i = titleRow.getFirstCellNum(); i < columnNum; i++) {
                        Cell columnCell = titleRow.getCell(i);
                        columnCell.setCellType(Cell.CELL_TYPE_STRING);
                        String cellName = columnCell.getStringCellValue();
                        if (cellName != null) {
                            if (!cellName.startsWith("@")) {
                                cellNameList.add(cellName);
                                columnIndexList.add(i);
                            }
                        }
                    }
                    
                    /* 生成INSERT语句，建表，导入数据前对表进行操作*/
                    getSql(tableName, cellNameList);
                    doBeforeImport(tableName, disableConsStrSet, enableConsStrSet, disableIndexesStrSet, enableIndexesStrSet);

                    ps = conn.prepareStatement(sql);
                    /* getFirstRowNum()返回0,getLastRowNum()返回sheet的总行数  - 1 */
                    for (int rownum = titleNum; rownum <= sheet.getLastRowNum(); rownum++) {
                        XSSFRow row = sheet.getRow(rownum);
                        if (null != row) {
                            int paramIndex = 1;
                            for(Integer i : columnIndexList){
                                Cell cell = row.getCell(i);
                                if (cell == null) {
                                    ps.setObject(paramIndex, null);
                                } else {
                                    cell.setCellType(Cell.CELL_TYPE_STRING);
                                    ps.setObject(paramIndex, getValue(cell.getStringCellValue(), i));
                                }
                                paramIndex++;
                            }
                            ps.addBatch();
                            count ++;
                            if (count == 10000) {
                                ps.executeBatch();
                                ps.clearBatch();
                                count = 0;
                                recordCount += 10000;
                                if (recordCount % 1000000 == 0) {
                                    System.out.println("      已插入" + recordCount + "条记录.");                    
                                }
                            }
                        }
                    }
                    ps.executeBatch();
                    ps.clearBatch();
                    recordCount += count;
                    doAfterImport(tableName, filename, sheetName);
                    Date endTime = new Date();
                    elapsedTime = endTime.getTime() - startTime.getTime();
                    System.out.println("    导入完成.记录数：" + recordCount + "  用时：" + elapsedTime + "ms");
                    System.out.println(String.format("%-100s", "-").replace(' ', '-'));
                    recordCount = 0;
                    count = 0;
                }
		    }
		    
            importResult = new ImportResult(true, recordCount, elapsedTime);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("  导入失败");
			importResult = new ImportResult(false);
		} finally {
			DbUtils.closeQuietly(ps);
			try {
                fis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
		}
		
		return importResult;
	}
	
}